﻿//2010-05-20    新增網頁錯誤判斷                By Victor
//2010-06-21    新增IP格式判斷                  By Victor
//2010-12-09    允許IP為127.0.0.1              By Victor

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

//Using Namespace
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    /// <summary>
    /// 可跨專案使用之共用函示
    /// </summary>
    public class CommonFun
    {
        private string strSCADADSN = System.Configuration.ConfigurationManager.AppSettings["strDSN"];
        private string strSCADADSN2 = System.Configuration.ConfigurationManager.AppSettings["strDSN2"];
        private string strSCADADSN3 = System.Configuration.ConfigurationManager.AppSettings["strDSN3"];

        //處理網頁錯誤
        //Edit by Victor
        #region public class Utilitie       //攔截網頁錯誤
        public class Utilities
        {
            public static Exception LastError;
        }
        #endregion


        //功能說明：常用自訂函示

        //Edit  By  Victor
        #region public DataTable GenDataTable(string strSQL)       //產生DataTable
        public DataTable GenDataTable(string strSQL)
        {
            try
            {
                SqlConnection conn = new SqlConnection();               //MS SQL
                conn.ConnectionString = strSCADADSN;

                DataTable myDataTable = new DataTable();
                SqlDataAdapter QDataAdapter = new SqlDataAdapter(strSQL, conn);
                QDataAdapter.Fill(myDataTable);

                QDataAdapter.Dispose();
                conn.Close();
                return myDataTable;
            }
            catch
            {

                return null;
            }
        }
        #endregion

        #region public DataTable GenDataTable2(string strSQL)       //產生DataTable
        public DataTable GenDataTable2(string strSQL)
        {
            try
            {
                SqlConnection conn = new SqlConnection();               //MS SQL
                conn.ConnectionString = strSCADADSN2;

                DataTable myDataTable = new DataTable();
                SqlDataAdapter QDataAdapter = new SqlDataAdapter(strSQL, conn);
                QDataAdapter.Fill(myDataTable);

                QDataAdapter.Dispose();
                conn.Close();
                return myDataTable;
            }
            catch
            {

                return null;
            }
        }
        #endregion

        #region public DataTable GenDataTable3(string strSQL)       //產生DataTable
        public DataTable GenDataTable3(string strSQL)
        {
            try
            {
                SqlConnection conn = new SqlConnection();               //MS SQL
                conn.ConnectionString = strSCADADSN3;

                DataTable myDataTable = new DataTable();
                SqlDataAdapter QDataAdapter = new SqlDataAdapter(strSQL, conn);
                QDataAdapter.Fill(myDataTable);

                QDataAdapter.Dispose();
                conn.Close();
                return myDataTable;
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region public bool ExeSQL(string strSQL)      //執行SQL語法，傳回執行完成與否
        public bool ExeSQL(string strSQL)
        {
            bool bFlag = true;

            try
            {
                SqlConnection conn = new SqlConnection(strSCADADSN);
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);

                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                AddErrorMsg("ExeSQL", ex.ToString(), strSQL);
                bFlag = false;
            }

            return bFlag;
        }

        public bool ExeSQL(string strSQL, int intTimeOut)
        {
            bool bFlag = true;

            try
            {

                SqlConnection conn = new SqlConnection(strSCADADSN);
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);

                cmd.CommandTimeout = intTimeOut;
                cmd.ExecuteNonQuery();
                conn.Close();

            }
            catch (Exception ex)
            {
                AddErrorMsg("ExeSQL", ex.ToString(), strSQL);
                bFlag = false;
            }

            return bFlag;
        }
        #endregion


        #region public bool ExeSQL2(string strSQL)      //執行SQL語法，傳回執行完成與否
        public bool ExeSQL2(string strSQL)
        {
            bool bFlag = true;

            try
            {
                SqlConnection conn = new SqlConnection(strSCADADSN2);
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);

                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                AddErrorMsg("ExeSQL", ex.ToString(), strSQL);
                bFlag = false;
            }

            return bFlag;
        }

        public bool ExeSQL2(string strSQL, int intTimeOut)
        {
            bool bFlag = true;

            try
            {

                SqlConnection conn = new SqlConnection(strSCADADSN2);
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);

                cmd.CommandTimeout = intTimeOut;
                cmd.ExecuteNonQuery();
                conn.Close();

            }
            catch (Exception ex)
            {
                AddErrorMsg("ExeSQL", ex.ToString(), strSQL);
                bFlag = false;
            }

            return bFlag;
        }
        #endregion

        #region public bool AddErrorMsg(string ClassName, string MethodName, string ErrorMsg, string SQLCommand)       //新增錯誤訊息
        /// <summary>
        /// 新增錯誤訊息
        /// </summary>
        /// <param name="MethodName">函示名稱</param>
        /// <param name="ErrorMsg">錯誤訊息</param>
        /// <param name="SQLCommand">SQL 指令</param>
        /// <returns>回傳寫入成功與否</returns>
        public bool AddErrorMsg(string MethodName, string ErrorMsg, string SQLCommand)
        {
            try
            {
                string ClassName = "BasicFun";
                string strSQL = "Insert into Web_ErrorMsg(ClassName, MethodName, ErrorMsg, SQLCommand) Values(@ClassName, @MethodName, @ErrorMsg, @SQLCommand) ";

                SqlConnection conn = new SqlConnection(strSCADADSN);
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);

                cmd.Parameters.AddWithValue("@ClassName", ClassName);
                cmd.Parameters.AddWithValue("@MethodName", MethodName);
                cmd.Parameters.AddWithValue("@ErrorMsg", ErrorMsg);
                cmd.Parameters.AddWithValue("@SQLCommand", SQLCommand);
                cmd.ExecuteNonQuery();
                conn.Close();

                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion


        //功能說明：資料型態檢查  
        //Edit By Victor
        #region IsDate(string strInputDate)    判斷是否為日期格式
        public bool IsDate(string strInputDate)
        {
            bool bFlag;
            try
            {
                DateTime TestDate = DateTime.Parse(strInputDate);
                bFlag = true;
            }
            catch//(Exception e)
            {
                bFlag = false;
            }
            return bFlag;
        }
        #endregion

        //Edit By Victor
        #region IsIP(string strInputDate)    判斷是否為IP格式
        public bool IsIP(string strInputDate)
        {
            bool bFlag;
            try
            {
                if (System.Text.RegularExpressions.Regex.IsMatch(strInputDate, "[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}") && strInputDate != "0.0.0.0" && strInputDate !="255.255.255.255")
                {
                    string[] strArrIP = strInputDate.Split('.');
                    if (strArrIP.Length == 4 || strArrIP.Length == 6)
                    {
                        if (System.Int32.Parse(strArrIP[0]) < 256 && System.Int32.Parse(strArrIP[1]) < 256 & System.Int32.Parse(strArrIP[2]) < 256 & System.Int32.Parse(strArrIP[3]) < 256)
                        {
                            bFlag = true;
                        }
                        else
                        {
                            bFlag = false;
                        }
                    }
                    else
                    {
                        bFlag = false;
                    }
                }
                else
                {
                    bFlag = false;
                }
            }
            catch//(Exception e)
            {
                bFlag = false;
            }
            return bFlag;
        }
        #endregion       

        //Edit By May      判斷是否為float
        #region isFloat(string strInputFloat)
        public bool isFloat(string strInputFloat)
        {
            bool bFlag;
            try
            {
                float TestFloat = float.Parse(strInputFloat);
                bFlag = true;
            }
            catch//(Exception e)
            {
                bFlag = false;
            }
            return bFlag;
        }
        #endregion

        //Edit by Victor    判斷是否為Email正確格式
        #region IsMailAddress(string MailAddress)
        /// <summary>
        /// 判斷字串是否為正確的電子郵件地址
        /// </summary>
        /// <param name="MailAddress">電子郵件地址</param>
        /// <returns>傳回布林值</returns>
        public bool IsMailAddress(string MailAddress)
        {
            bool bFinal;
            try
            {
                System.Net.Mail.MailAddress mail = new System.Net.Mail.MailAddress(MailAddress);
                bFinal = true;
            }
            catch
            {
                bFinal = false;
            }

            return bFinal;
        }
        #endregion 

    }
}
